package com.ywu.application.standard.dataSource.source.sql;

import com.ywu.application.standard.dataSource.dto.*;
import com.ywu.boot.context.YwuContextHolder;
import com.ywu.boot.exception.YwuRuntimeException;
import com.ywu.application.standard.dataSource.data.YwuDataTableRepository;
import com.ywu.application.standard.dataSource.entity.YwuDataExecEntity;
import com.ywu.application.standard.dataSource.entity.YwuDataTableEntity;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;

import java.util.*;

/**
 * @ClassName YwuBuildSqlUtil
 * @Description TODO
 * @Author GroundDemo
 * @Date 2024/10/31 19:16
 * @Version 1.0
 **/
@Component
public class YwuBuildSqlUtil {
    @Autowired
    private YwuDataTableRepository dataTableRepository;

    private static final Map<String, String> operationMap = new HashMap<>();

    static {
        operationMap.put("eq", " = ");
        operationMap.put("neq", " != ");
        operationMap.put("bg", " > ");
        operationMap.put("bgeq", " >= ");
        operationMap.put("lg", " < ");
        operationMap.put("lgeq", " <= ");
        operationMap.put("like", " LIKE ");
        operationMap.put("noLike", " NOT LIKE ");
        operationMap.put("null", " IS NULL ");
        operationMap.put("nonNull", " NOT NULL ");
    }

    /**
    * @Author GroundDemo
    * @Description 构建待执行的sql参数
    * @Date 19:22 2024/10/31
    * @Param [ywuDataExecEntity]
    * @return java.lang.String
    **/
    public String buildSql(YwuDataExecEntity ywuDataExecEntity) {
        String resSql = "";
        String execType = ywuDataExecEntity.getExecType();
        switch (execType) {
            case YwuDataExecEntity.INSERT_EXEC_TYPE:
                resSql = buildInsertSql(ywuDataExecEntity);
                break;
            case YwuDataExecEntity.UPDATE_EXEC_TYPE:
                resSql = buildUpdateSql(ywuDataExecEntity);
                break;
            case YwuDataExecEntity.DELETE_EXEC_TYPE:
                resSql = buildDeleteSql(ywuDataExecEntity);
                break;
            case YwuDataExecEntity.QUERY_EXEC_TYPE:
                resSql = buildQuerySql(ywuDataExecEntity);
                break;
            case YwuDataExecEntity.COMPLEX_QUERY_EXEC_TYPE:
                resSql = buildComplexQuerySql(ywuDataExecEntity);
                break;
            default:
                break;
        }
        return resSql;
    }

    /**
    * @Author GroundDemo
    * @Description 构建Query类型的sql
    * @Date 19:25 2024/10/31
    * @Param [ywuDataExecEntity]
    * @return java.lang.String
    **/
    private String buildQuerySql(YwuDataExecEntity ywuDataExecEntity) {
        // 查询数据表名
        YwuDataTableEntity search = new YwuDataTableEntity();
        search.setCode(ywuDataExecEntity.getDataTableCode());
        search.setTenantId(YwuContextHolder.getCurrentContext().getYwuContextTenant().getTenantId());
        YwuDataTableEntity queryRes = dataTableRepository.getEntity(search);
        String templateSql = "SELECT %s FROM `%s` %s;";
        String fieldsSql = String.join(",", ywuDataExecEntity.getExecFields());
        if (StringUtils.isEmpty(fieldsSql)) {
            fieldsSql = "*";
        }
        // 构建条件字段
        String conditionSql = buildConditionSql(ywuDataExecEntity);
        return String.format(templateSql, fieldsSql, queryRes.getTableName(), conditionSql);
    }

    /**
    * @Author GroundDemo
    * @Description 构建ComplexQuery的复杂查询类型的sql
    * @Date 19:25 2024/10/31
    * @Param [ywuDataExecEntity]
    * @return java.lang.String
    **/
    private String buildComplexQuerySql(YwuDataExecEntity ywuDataExecEntity) {
        String templateSql = "SELECT %s FROM %s %s %s %s %s %s;";
        // 构建复杂操作字段
        String complexQueryFieldSql = buildComplexQueryFieldSql(ywuDataExecEntity);
        // 构建复杂查询的连接表名
        String complexQueryTableJoinSql = buildComplexQueryTableJoinSql(ywuDataExecEntity);
        // 构建查询条件
        String complexQueryConditionSql = buildConditionSql(ywuDataExecEntity);
        // 构建分组字段
        String complexQueryGroupSql = buildComplexQueryGroupSql(ywuDataExecEntity);
        // 构建分组条件字段
        String complexQueryGroupConditionSql = buildComplexQueryGroupConditionSql(ywuDataExecEntity);
        // 构建排序字段
        String complexQuerySortSql = buildComplexQuerySortSql(ywuDataExecEntity);
        // 构建分页字段
        String complexQueryPageSql = buildComplexQueryPageSql(ywuDataExecEntity);
        // 构建完整sql
        return String.format(templateSql, complexQueryFieldSql, complexQueryTableJoinSql, complexQueryConditionSql,
                complexQueryGroupSql, complexQueryGroupConditionSql, complexQuerySortSql, complexQueryPageSql);
    }

    /**
    * @Author GroundDemo
    * @Description 构建复杂查询的分页字段
    * @Date 19:25 2024/10/31
    * @Param [ywuDataExecEntity]
    * @return java.lang.String
    **/
    private String buildComplexQueryPageSql(YwuDataExecEntity ywuDataExecEntity) {
        if (!ywuDataExecEntity.getEnablePageInfo()) {
            return "";
        }
        return " LIMIT ?, ? ;";
    }

    /**
    * @Author GroundDemo
    * @Description 构建复杂查询的排序字段
    * @Date 19:25 2024/10/31
    * @Param [ywuDataExecEntity]
    * @return java.lang.String
    **/
    private String buildComplexQuerySortSql(YwuDataExecEntity ywuDataExecEntity) {
        if (CollectionUtils.isEmpty(ywuDataExecEntity.getSortFields())) {
            return "";
        }
        StringBuilder resSql = new StringBuilder();
        resSql.append(" ORDER BY ");
        for (YwuDataExecComplexQueryOrderDto sortField : ywuDataExecEntity.getSortFields()) {
            resSql.append(sortField.getFieldName());
            resSql.append(" ");
            resSql.append(sortField.getOrderType());
            resSql.append(",");
        }
        // 去除最后一个逗号
        String resStr = resSql.toString();
        if (resStr.endsWith(",")) {
            resStr = resStr.substring(0, resStr.length() - 1);
        }
        return resStr;
    }

    /**
    * @Author GroundDemo
    * @Description 构建复杂查询的分组条件字段
    * @Date 19:25 2024/10/31
    * @Param [ywuDataExecEntity]
    * @return java.lang.String
    **/
    private String buildComplexQueryGroupConditionSql(YwuDataExecEntity ywuDataExecEntity) {
        if (CollectionUtils.isEmpty(ywuDataExecEntity.getGroupCondition())) {
            ywuDataExecEntity.setGroupConditionFields("");
            return "";
        }
        // 存储后续要传入的参数字段
        List<String> fields = new ArrayList<>();
        // 递归构建多层级的条件
        List<YwuDataExecComplexQueryGroupConditionDto> groupCondition = ywuDataExecEntity.getGroupCondition();
        StringBuilder lastCondition = new StringBuilder();
        String resSql = parseGroupConditionSql(groupCondition, fields, lastCondition);
        if (StringUtils.isEmpty(resSql)) {
            ywuDataExecEntity.setGroupConditionFields("");
            return "";
        }
        String res = " HAVING " + resSql;
        ywuDataExecEntity.setGroupConditionFields(String.join(",", fields));
        return res;
    }

    /**
    * @Author GroundDemo
    * @Description 构建复杂查询的分组字段
    * @Date 19:25 2024/10/31
    * @Param [ywuDataExecEntity]
    * @return java.lang.String
    **/
    private String buildComplexQueryGroupSql(YwuDataExecEntity ywuDataExecEntity) {
        if (CollectionUtils.isEmpty(ywuDataExecEntity.getGroupFields())) {
            return "";
        }
        StringBuilder res = new StringBuilder();
        res.append(" GROUP BY ");
        for (String groupField : ywuDataExecEntity.getGroupFields()) {
            res.append(groupField).append(",");
        }
        // 如果最后以逗号结尾，去除最后的逗号
        String resStr = res.toString();
        if (resStr.endsWith(",")) {
            resStr = resStr.substring(0, resStr.length() - 1);
        }
        return resStr;
    }

    /**
    * @Author GroundDemo
    * @Description 构建复杂查询的连接表名
    * @Date 19:25 2024/10/31
    * @Param [ywuDataExecEntity]
    * @return java.lang.String
    **/
    private String buildComplexQueryTableJoinSql(YwuDataExecEntity ywuDataExecEntity) {
        StringBuilder res = new StringBuilder();
        List<String> dataTableCodeList = ywuDataExecEntity.getDataTableCodeList();
        if (CollectionUtils.isEmpty(dataTableCodeList)) {
            throw new YwuRuntimeException("data.table.code.list.empty");
        }
        int index = 1;
        for (String dataTableCode : ywuDataExecEntity.getDataTableCodeList()) {
            // 根据编码查询表名称
            YwuDataTableEntity search = new YwuDataTableEntity();
            search.setCode(dataTableCode);
            search.setTenantId(YwuContextHolder.getCurrentContext().getYwuContextTenant().getTenantId());
            YwuDataTableEntity queryRes = dataTableRepository.getEntity(search);
            if (Objects.isNull(queryRes)) {
                throw new YwuRuntimeException("data.table.not.exists");
            }
            // 第一张表直接填入即可，无需连接条件
            if (index == 1) {
                res.append(" `").append(queryRes.getTableName()).append("`");
            } else {
                res.append(" LEFT JOIN `").append(queryRes.getTableName()).append("` ON ");
                // 找到右表为当前表字段进行连接
                boolean isFound = false;
                List<YwuDataExecComplexQueryTableJoinDto> complexQueryTableJoinList = ywuDataExecEntity.getComplexQueryTableJoinList();
                for (YwuDataExecComplexQueryTableJoinDto join : complexQueryTableJoinList) {
                    String rightTableName = join.getRightTableFieldName().split("\\.")[0];
                    if (rightTableName.equals(queryRes.getTableName())) {
                        res.append(join.getLeftTableFieldName()).append(" = ").append(join.getRightTableFieldName());
                        isFound = true;
                        break;
                    }
                }
                // 多表必须要又连接字段，没有连接字段直接报错
                if (!isFound) {
                    throw new YwuRuntimeException("complex.query.table.join.field.empty");
                }
            }
            index++;
        }
        return res.toString();
    }

    /**
    * @Author GroundDemo
    * @Description 构建复杂查询字段的sql
    * @Date 19:25 2024/10/31
    * @Param [ywuDataExecEntity]
    * @return java.lang.String
    **/
    private String buildComplexQueryFieldSql(YwuDataExecEntity ywuDataExecEntity) {
        List<YwuDataExecComplexQueryFieldDto> complexQueryFieldList = ywuDataExecEntity.getComplexQueryFieldList();
        if (CollectionUtils.isEmpty(complexQueryFieldList)) {
            return "*";
        }
        StringBuilder res = new StringBuilder();
        for (YwuDataExecComplexQueryFieldDto field : complexQueryFieldList) {
            String temp = "";
            if (field.getAddAggFunc()) {
                temp = field.getFunctionName() + "(" + field.getFieldName() + ")";
            } else {
                temp = field.getFieldName();
            }
            // 如果没有别名，默认填充字段名
            if (StringUtils.isEmpty(field.getAlias())) {
                temp = temp + " AS '" + field.getFieldName() + "'";
            } else {
                temp = temp + " AS '" + field.getAlias() + "'";
            }
            res.append(temp).append(",");
        }
        // 如果最后添加的字段有逗号，则删除
        String resStr = res.toString();
        if (resStr.endsWith(",")) {
            resStr = resStr.substring(0, resStr.length() - 1);
        }
        return resStr;
    }

    /**
    * @Author GroundDemo
    * @Description 解析复杂查询的分组条件
    * @Date 19:25 2024/10/31
    * @Param [groupCondition, fields, lastCondition]
    * @return java.lang.String
    **/
    private String parseGroupConditionSql(List<YwuDataExecComplexQueryGroupConditionDto> groupCondition, List<String> fields, StringBuilder lastCondition) {
        if (CollectionUtils.isEmpty(groupCondition)) {
            return "";
        }
        StringBuilder resSql = new StringBuilder();
        for (YwuDataExecComplexQueryGroupConditionDto conditionDto : groupCondition) {
            if (Objects.nonNull(conditionDto.getIsSon()) && conditionDto.getIsSon()) {
                // 子条件
                String templateSql = " (%s) %s ";
                String execResSql = parseGroupConditionSql(conditionDto.getSonList(), fields, lastCondition);
                resSql.append(String.format(templateSql, execResSql, lastCondition));
            } else {
                String templateSql = " %s %s %s %s ";
                String fieldName = conditionDto.getFieldName();
                // 如果使用聚合函数，需要调整字段名称
                if (conditionDto.getAddAggFunc()) {
                    fieldName = conditionDto.getFunctionName() + "(" + fieldName + ")";
                }
                // 获取操作符
                String operation = operationMap.get(conditionDto.getOperationType());
                // 获取连接条件
                String condition = conditionDto.getConditionType();
                // 清空后重置
                lastCondition.setLength(0);
                lastCondition.append(condition);
                // 判断是否需要聚合函数
                String value = "?";
                if ("like".equals(conditionDto.getOperationType()) || "noLike".equals(conditionDto.getOperationType())) {
                    value = "CONCAT('%', ?, '%')";
                } else if ("null".equals(conditionDto.getOperationType()) || "nonNull".equals(conditionDto.getOperationType())) {
                    value = "";
                }
                resSql.append(String.format(templateSql, fieldName, operation, value, condition));
                fields.add(fieldName);
            }
        }
        String res = resSql.toString().trim();
        if (res.endsWith("OR")) {
            res = res.substring(0, res.length() - 2);
        } else if (res.endsWith("AND")) {
            res = res.substring(0, res.length() - 3);
        }
        return res;
    }

    /**
    * @Author GroundDemo
    * @Description 构建Delete类型的sql
    * @Date 19:25 2024/10/31
    * @Param [ywuDataExecEntity]
    * @return java.lang.String
    **/
    private String buildDeleteSql(YwuDataExecEntity ywuDataExecEntity) {
        List<YwuDataConditionDto> execCondition = ywuDataExecEntity.getExecCondition();
        // 删除数据时必须要有条件，否则报错
        if (CollectionUtils.isEmpty(execCondition)) {
            throw new YwuRuntimeException("delete.operation.condition.can.not.be.empty");
        }
        // 查询数据表名
        YwuDataTableEntity search = new YwuDataTableEntity();
        search.setCode(ywuDataExecEntity.getDataTableCode());
        search.setTenantId(YwuContextHolder.getCurrentContext().getYwuContextTenant().getTenantId());
        YwuDataTableEntity queryRes = dataTableRepository.getEntity(search);
        String templateSql = "DELETE FROM `%s` %s;";
        // 构建条件字段
        String conditionSql = buildConditionSql(ywuDataExecEntity);
        return String.format(templateSql, queryRes.getTableName(), conditionSql);
    }

    /**
    * @Author GroundDemo
    * @Description 构建Update类型的sql
    * @Date 19:25 2024/10/31
    * @Param [ywuDataExecEntity]
    * @return java.lang.String
    **/
    private String buildUpdateSql(YwuDataExecEntity ywuDataExecEntity) {
        // 查询数据表名
        YwuDataTableEntity search = new YwuDataTableEntity();
        search.setCode(ywuDataExecEntity.getDataTableCode());
        search.setTenantId(YwuContextHolder.getCurrentContext().getYwuContextTenant().getTenantId());
        YwuDataTableEntity queryRes = dataTableRepository.getEntity(search);
        String startSql = "UPDATE `%s` SET %s %s;";
        // 构建set字段
        StringBuilder setSql = new StringBuilder();
        List<String> execFields = ywuDataExecEntity.getExecFields();
        for (int index = 0; index < execFields.size(); index++) {
            String field = execFields.get(index);
            setSql.append(field).append(" = ? ");
            if (index != execFields.size() - 1) {
                setSql.append(",");
            }
        }
        // 构建条件字段
        String conditionSql = buildConditionSql(ywuDataExecEntity);
        return String.format(startSql, queryRes.getTableName(), setSql, conditionSql);
    }

    /**
    * @Author GroundDemo
    * @Description 构建Insert类型的sql
    * @Date 19:25 2024/10/31
    * @Param [ywuDataExecEntity]
    * @return java.lang.String
    **/
    private String buildInsertSql(YwuDataExecEntity ywuDataExecEntity) {
        // 查询数据表名
        YwuDataTableEntity search = new YwuDataTableEntity();
        search.setCode(ywuDataExecEntity.getDataTableCode());
        search.setTenantId(YwuContextHolder.getCurrentContext().getYwuContextTenant().getTenantId());
        YwuDataTableEntity queryRes = dataTableRepository.getEntity(search);
        String startSql = "INSERT INTO `%s` (%s) VALUES (%s);";
        // 构建字段
        List<String> execFields = ywuDataExecEntity.getExecFields();
        String fieldsStr = String.join(",", execFields);
        // 构建占位符
        List<String> placeHolderField = new ArrayList<>();
        execFields.forEach(res -> {
            placeHolderField.add("?");
        });
        String placeHolderStr = String.join(",", placeHolderField);
        return String.format(startSql, queryRes.getTableName(), fieldsStr, placeHolderStr);
    }

    /**
    * @Author GroundDemo
    * @Description 构建where条件的语句
    * @Date 19:46 2024/10/31
    * @Param [ywuDataExecEntity]
    * @return java.lang.String
    **/
    private String buildConditionSql(YwuDataExecEntity ywuDataExecEntity) {
        List<String> fields = new ArrayList<>();
        List<YwuDataConditionDto> execCondition = ywuDataExecEntity.getExecCondition();
        // 递归构建多层级的条件
        StringBuilder lastCondition = new StringBuilder();
        String resSql = parseConditionSql(execCondition, fields, lastCondition);
        if (StringUtils.isEmpty(resSql)) {
            ywuDataExecEntity.setConditionFields("");
            return "";
        }
        resSql = "WHERE " + resSql;
        ywuDataExecEntity.setConditionFields(String.join(",", fields));
        return resSql;
    }

    /**
    * @Author GroundDemo
    * @Description 递归生成查询条件sql
    * @Date 19:59 2024/10/31
    * @Param [execCondition, fields, lastCondition]
    * @return void
    **/
    public static String parseConditionSql(List<YwuDataConditionDto> execCondition, List<String> fields, StringBuilder lastCondition) {
        if (CollectionUtils.isEmpty(execCondition)) {
            return "";
        }
        StringBuilder resSql = new StringBuilder();
        for (YwuDataConditionDto conditionDto : execCondition) {
            if (Objects.nonNull(conditionDto.getIsSon()) && conditionDto.getIsSon()) {
                // 子条件
                String templateSql = " (%s) %s ";
                String execResSql = parseConditionSql(conditionDto.getSonList(), fields, lastCondition);
                resSql.append(String.format(templateSql, execResSql, lastCondition));
            } else {
                String templateSql = " %s %s %s %s ";
                String fieldName = conditionDto.getFieldName();
                String operation = operationMap.get(conditionDto.getOperationType());
                String value = "?";
                if (conditionDto.getOperationType().equals("like") || conditionDto.getOperationType().equals("noLike")) {
                    value = "CONCAT('%', ?, '%')";
                } else if (conditionDto.getOperationType().equals("null") || conditionDto.getOperationType().equals("nonNull")) {
                    value = "";
                }
                String condition = conditionDto.getConditionType();
                // 清空后重置
                lastCondition.setLength(0);
                lastCondition.append(condition);
                resSql.append(String.format(templateSql, fieldName, operation, value, condition));
                fields.add(fieldName);
            }
        }
        String res = resSql.toString().trim();
        if (res.endsWith("AND")) {
            res = res.substring(0, res.length() - 3);
        } else if (res.endsWith("OR")) {
            res = res.substring(0, res.length() - 2);
        }
        return res;
    }
}
